﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using OSGeo.OGR;
using System.Data.SQLite;

namespace GDALSSIS.OGR.Intersection
{
    [DtsPipelineComponent(
        DisplayName = "OGR Intersection",
        ComponentType = ComponentType.Transform
    )]

    public class Intersection : PipelineComponent
    {
        private PipelineBuffer outputBuffer;
        private SQLiteConnection db;
        int inputCount = 0;

        private struct columnInfo
        {
            public int bufferColumnIndex;
            public string columnName;
            public string SQLiteDatatype;
            public bool isGeom;
        }

        private List<columnInfo> ouptutColumnList = new List<columnInfo>();
        private Dictionary<int, List<columnInfo>> inputColumnListDictionary = new Dictionary<int, List<columnInfo>>();

        public override void ProvideComponentProperties()
        {
            //Specify that the component has an error output
            ComponentMetaData.UsesDispositions = true;

            //Add input 1 geometry column property
            IDTSCustomProperty100 input1GeomColumn = ComponentMetaData.CustomPropertyCollection.New();
            input1GeomColumn.Name = "Input 1 Geometry Column";
            input1GeomColumn.Description = "Input 1 Geometry Column";
            input1GeomColumn.Value = "OGRGeometry";

            //Add input 2 geometry column property
            IDTSCustomProperty100 input2GeomColumn = ComponentMetaData.CustomPropertyCollection.New();
            input2GeomColumn.Name = "Input 2 Geometry Column";
            input2GeomColumn.Description = "Input 2 Geometry Column";
            input2GeomColumn.Value = "OGRGeometry";

            //Add input1
            IDTSInput100 input1 = ComponentMetaData.InputCollection.New();
            input1.Name = "Input 1";

            //Add input2
            IDTSInput100 input2 = ComponentMetaData.InputCollection.New();
            input2.Name = "Input 2";

            //Add the output
            IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
            output.Name = "Output";

            //Add output geom column
            IDTSOutputColumn100 geomCol = output.OutputColumnCollection.New();
            geomCol.Name = "OGRGeometry";
            geomCol.Description = "OGRGeometry";
            geomCol.SetDataTypeProperties(DataType.DT_IMAGE, 0, 0, 0, 0);

            //Add the error output
            IDTSOutput100 errorOutput = ComponentMetaData.OutputCollection.New();
            errorOutput.IsErrorOut = true;
            errorOutput.Name = "ErrorOutput";
        }

        public override void OnInputPathAttached(int inputID)
        {
            IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);

            int defaultOutputID = -1;
            int errorOutputID = -1;
            int errorOutputIndex = -1;

            GetErrorOutputInfo(ref errorOutputID, ref errorOutputIndex);

            if (errorOutputIndex == 0)
                defaultOutputID = ComponentMetaData.OutputCollection[1].ID;
            else
                defaultOutputID = ComponentMetaData.OutputCollection[0].ID;

            IDTSOutput100 output = ComponentMetaData.OutputCollection.GetObjectByID(defaultOutputID);

            IDTSVirtualInput100 vInput = input.GetVirtualInput();

            //Add input columns
            foreach (IDTSVirtualInputColumn100 vCol in vInput.VirtualInputColumnCollection)
            {
                SetUsageType(inputID, vInput, vCol.LineageID, DTSUsageType.UT_READONLY);

            }
            //Add output Columns
            foreach (IDTSVirtualInputColumn100 vCol in vInput.VirtualInputColumnCollection)
            {
                if (vCol.Name != (string)ComponentMetaData.CustomPropertyCollection["Input 1 Geometry Column"].Value && vCol.Name != (string)ComponentMetaData.CustomPropertyCollection["Input 2 Geometry Column"].Value)
                {
                    IDTSOutputColumn100 outCol = output.OutputColumnCollection.New();
                    outCol.Name = vCol.Name;
                    outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage);
                }
            }
        }

        public override DTSValidationStatus Validate()
        {
            return base.Validate();
        }

        public override void PreExecute()
        {
            //get input column information
            foreach (IDTSInput100 input in ComponentMetaData.InputCollection)
            {
                List<columnInfo> inputColumnList = new List<columnInfo>();
                foreach (IDTSInputColumn100 col in input.InputColumnCollection)
                {
                    columnInfo ci = new columnInfo();
                    if (col.Name == (string)ComponentMetaData.CustomPropertyCollection[input.Name + " Geometry Column"].Value)
                    {
                        ci.isGeom = true;
                    }
                    else
                    {
                        ci.isGeom = false;
                    }
                    ci.bufferColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, col.LineageID);
                    ci.columnName = col.Name;
                    ci.SQLiteDatatype = this.getSQLiteDataType(col.DataType);
                    inputColumnList.Add(ci);
                }
                //add input column list to input column list dictionary
                this.inputColumnListDictionary.Add(input.ID, inputColumnList);
            }

            //get output column information
            IDTSOutput100 defaultOutput = ComponentMetaData.OutputCollection[0];
            foreach (IDTSOutputColumn100 col in defaultOutput.OutputColumnCollection)
            {
                columnInfo ci = new columnInfo();
                if (col.Name == "OGRGeometry")
                {
                    ci.isGeom = true;
                }
                else
                {
                    ci.isGeom = false;
                }
                ci.bufferColumnIndex = BufferManager.FindColumnByLineageID(defaultOutput.Buffer, col.LineageID);
                ci.columnName = col.Name;
                this.ouptutColumnList.Add(ci);
            }

            //create spatialite db
            this.db = new SQLiteConnection("Data Source=:memory:");
            db.Open();

            SQLiteCommand command;
            command = new SQLiteCommand("SELECT load_extension('spatialite.dll');", this.db);
            command.ExecuteNonQuery();

            command = new SQLiteCommand("SELECT InitSpatialMetadata();", this.db);
            command.ExecuteNonQuery();

            //create tables
            foreach (KeyValuePair<int, List<columnInfo>> pair in this.inputColumnListDictionary)
            {
                string sqlStmt = "";
                string geometryColumn = "";
                foreach (columnInfo ci in pair.Value)
                {
                    if (ci.isGeom)
                    {
                        geometryColumn = ci.columnName;
                    }
                    else
                    {
                        sqlStmt = sqlStmt + "[" + ci.columnName + "] " + ci.SQLiteDatatype + ",";
                    }
                }

                //create table
                sqlStmt = "CREATE TABLE input_" + pair.Key + "(" + sqlStmt.TrimEnd(',') + ")";
                command = new SQLiteCommand(sqlStmt, this.db);
                command.ExecuteNonQuery();

                //add geometry column
                sqlStmt = "SELECT AddGeometryColumn('input_" + pair.Key + "', '" + geometryColumn + "', -1, 'GEOMETRY', 'XY')";
                command = new SQLiteCommand(sqlStmt, this.db);
                command.ExecuteNonQuery();
            }
        }

        public override void ProcessInput(int inputID, PipelineBuffer buffer)
        {
            //prepare insert statement
            SQLiteTransaction tran = this.db.BeginTransaction();
            SQLiteCommand command = new SQLiteCommand(this.db);
            string sqlStmt = "";
            string sqlStmtParams = "";

            foreach (columnInfo ci in this.inputColumnListDictionary[inputID])
            {
                sqlStmt = sqlStmt + ci.columnName + ",";
                if (ci.isGeom)
                {
                    sqlStmtParams = sqlStmtParams + "ST_GeomFromWKB(@" + ci.columnName + ")" + ",";
                }
                else
                {
                    sqlStmtParams = sqlStmtParams + "@" + ci.columnName + ",";
                }
            }

            sqlStmt = "INSERT INTO input_" + inputID + "(" + sqlStmt.TrimEnd(',') + ") VALUES(" + sqlStmtParams.TrimEnd(',') + ")";

            command.CommandText = sqlStmt;
            command.Prepare();

            while (buffer.NextRow())
            {
                //inset buffer data into sqlite table 
                foreach (columnInfo ci in this.inputColumnListDictionary[inputID])
                {
                    if (ci.isGeom)
                    {
                        byte[] geomBytes = new byte[buffer.GetBlobLength(ci.bufferColumnIndex)];
                        geomBytes = buffer.GetBlobData(ci.bufferColumnIndex, 0, geomBytes.Length);
                        command.Parameters.AddWithValue("@" + ci.columnName, geomBytes);
                    }
                    else
                    {
                        command.Parameters.AddWithValue("@" + ci.columnName, buffer[ci.bufferColumnIndex]);
                    }
                }
                command.ExecuteNonQuery();
            }

            tran.Commit();

            if (buffer.EndOfRowset)
            {
                this.inputCount++;
                if (this.inputCount == 2)
                {
                    List<int> keys = new List<int>(this.inputColumnListDictionary.Keys);

                    string tableName1 = "input_" + keys[0];
                    string tableName2 = "input_" + keys[1];
                    string columns1 = "";
                    string columns2 = "";
                    string geomColumn1 = "";
                    string geomColumn2 = "";

                    foreach (columnInfo ci in this.inputColumnListDictionary[keys[0]])
                    {
                        if (ci.isGeom)
                        {
                            geomColumn1 = ci.columnName;
                        }
                        else
                        {
                            columns1 = columns1 + tableName1 + "." + ci.columnName + ",";
                        }
                    }

                    foreach (columnInfo ci in this.inputColumnListDictionary[keys[1]])
                    {
                        if (ci.isGeom)
                        {
                            geomColumn2 = ci.columnName;
                        }
                        else
                        {
                            columns2 = columns2 + tableName2 + "." + ci.columnName + ",";
                        }
                    }

                    //create spatial index
                    sqlStmt = "SELECT CreateSpatialIndex('" + tableName1 + "', '" + geomColumn1 + "')";
                    command = new SQLiteCommand(sqlStmt, this.db);
                    command.ExecuteNonQuery();

                    //execute intersection
                    sqlStmt = "SELECT " + columns1 + columns2 + "ST_AsBinary(ST_Intersection(" + tableName1 + "." + geomColumn1 + "," + tableName2 + "." + geomColumn2 + ")) AS OGRGeometry" +
                        " FROM " + tableName1 + "," + tableName2 +
                        " WHERE ST_Intersects(" + tableName1 + "." + geomColumn1 + "," + tableName2 + "." + geomColumn2 + ")" +
                        " AND " + tableName1 + ".rowid IN (SELECT pkid FROM idx_" + tableName1 + "_" + geomColumn1 +
                        " WHERE   xmin <= MbrMaxX(" + tableName2 + "." + geomColumn2 + ")" +
                        " AND     ymin <= MbrMaxY(" + tableName2 + "." + geomColumn2 + ")" +
                        " AND     xmax >= MbrMinX(" + tableName2 + "." + geomColumn2 + ")" +
                        " AND     ymax >= MbrMinY(" + tableName2 + "." + geomColumn2 + "))";
                    command = new SQLiteCommand(sqlStmt, this.db);
                    SQLiteDataReader reader = command.ExecuteReader();

                    //write result set to outputBuffer
                    while (reader.Read())
                    {
                        outputBuffer.AddRow();
                        foreach (columnInfo ci in this.ouptutColumnList)
                        {
                            if (ci.columnName == "OGRGeometry")
                            {
                                int WKBIndex = reader.GetOrdinal("OGRGeometry");
                                byte[] geomBytes = (byte[])reader.GetValue(WKBIndex);
                                outputBuffer.AddBlobData(ci.bufferColumnIndex, geomBytes);
                            }
                            else
                            {
                                outputBuffer[ci.bufferColumnIndex] = reader[ci.columnName];
                            }
                        }
                    }

                    outputBuffer.SetEndOfRowset();
                    this.db.Close();

                }
            }
        }

        public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
        {
            outputBuffer = buffers[0];
            //errorBuffer = buffers[1];
        }

        private string getSQLiteDataType(DataType datatype)
        {
            return "";
        }
    }
}
